#Region " Imports "

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.ComponentModel
Imports System.Configuration.Install
Imports System.IO
Imports System.Reflection
Imports System.Configuration
Imports System.Web.Configuration

#End Region

Public Class DBCustomAction

    Private DBName As String = "BlazeApps"
    Private InstallSQLscripts As String() = New String() {"tables.sql", "functions.sql", "views.sql", "procedures.sql", "InsertBaseData.sql"}
    Private UpgradeSQLscripts As String() = New String() {"UpgradeDatabaseTo_1_3.sql", "UpgradeDatabaseDataTo_1_3.sql"}
    Private TargetASPNETVersion As String = "v2.0.50727"
    Private DBVersion As Decimal = 1.3

    Public Sub New()
        MyBase.New()

        'This call is required by the Component Designer.
        InitializeComponent()

        'Add initialization code after the call to InitializeComponent

    End Sub

    Public Overrides Sub Install(ByVal stateSaver As System.Collections.IDictionary)
        MyBase.Install(stateSaver)

        'Dim Server As String
        'Dim User As String
        'Dim Password As String
        'Dim AppFolder As String
        Dim VDir As String
        Dim Site As String
        'Dim ServerDB As String

        'Server = Context.Parameters("Server").Split("@"c)(0)
        'User = Context.Parameters("Server").Split("@"c)(1)
        'Password = Context.Parameters("Server").Split("@"c)(2)
        'AppFolder = Context.Parameters("Server").Split("@"c)(3)
        'VDir = Context.Parameters("Server").Split("@"c)(4)
        'Site = Context.Parameters("Server").Split("@"c)(5)
        'ServerDB = Context.Parameters("Server").Split("@"c)(6)

        'Server = Server
        'User = User.Split("="c)(1)
        'Password = Password.Split("="c)(1)
        'AppFolder = AppFolder.Split("="c)(1)
        'VDir = VDir.Split("="c)(1)
        'Site = Site.Split("="c)(1)
        'ServerDB = ServerDB.Split("="c)(1)

        'Context.Parameters("Server") = Server
        'Context.Parameters("Username") = User
        'Context.Parameters("Password") = Password
        'Context.Parameters("Folder") = AppFolder
        'Context.Parameters("VDir") = VDir
        'Context.Parameters("Site") = Site
        'Context.Parameters("ServerDB") = ServerDB

        VDir = Context.Parameters("Server").Split("@"c)(0)
        Site = Context.Parameters("Server").Split("@"c)(1)

        VDir = VDir
        Site = Site.Split("="c)(1)

        Context.Parameters("VDir") = VDir
        Context.Parameters("Site") = Site

        'VDir = Context.Parameters("[TARGETVDIR]")
        'Site = Context.Parameters("[TARGETSITE]")

        System.Diagnostics.Process.Start(String.Format("http://localhost/{0}/install/DBInstall.aspx", VDir))

        'If ServerDB = "1" Then
        '    'MsgBox("MySQL Server 5.0 Was the chosen option", MsgBoxStyle.OkOnly, "MySQL")
        '    System.Diagnostics.Process.Start(String.Format("http://localhost/{0}/install/DBInstall.aspx", VDir))
        'Else
        '    'MsgBox("MS SQL Server 2005, MS SQL Server Express Was the chosen option", MsgBoxStyle.OkOnly, "MS SQL")
        '    System.Diagnostics.Process.Start(String.Format("http://localhost/{0}/install/DBInstall.aspx", VDir))
        '    'Dim MasterConnectionString As String = String.Format("server={0};database=master;Integrated Security=SSPI;", Server)
        '    'Dim BlazeAppsConnectionString As String = String.Format("server={0};uID={1};pwd={2};database={3};Integrated Security=true;", Server, User, Password, DBName)
        '    'Dim WebConfigConnectionString As String = String.Format("server={0};uID={1};pwd={2};database={3};Integrated Security=false;", Server, User, Password, DBName)

        '    'MsgBox(String.Format("Checking to see if there is an application database."), MsgBoxStyle.OkOnly, "DB Check")

        '    'If IsDBBuilt(BlazeAppsConnectionString) Then
        '    '    If Not IsDBCurrent(DBVersion, BlazeAppsConnectionString) Then
        '    '        MsgBox("The application database is not the latest version.  An update will now be applied.", MsgBoxStyle.OkOnly, "DB Upgrade")
        '    '        ExecuteDatabaseScripts(UpgradeSQLscripts, BlazeAppsConnectionString)
        '    '    Else
        '    '        MsgBox(String.Format("The application database is the latest version.  No updates are required."), MsgBoxStyle.OkOnly, "DB Present")
        '    '    End If
        '    'Else
        '    '    MsgBox(String.Format("There is no application database present.  A new database will be built and configured."), MsgBoxStyle.OkOnly, "DB Install")
        '    '    BuildDatabase(MasterConnectionString)
        '    '    Sleep()
        '    '    GrantAccess(BlazeAppsConnectionString, User)
        '    '    Sleep()
        '    '    ExecuteDatabaseScripts(InstallSQLscripts, BlazeAppsConnectionString)
        '    'End If

        '    'Sleep()
        '    'GrantObjectAccess(True, BlazeAppsConnectionString, User)
        '    'Sleep()
        '    'SaveConnection(WebConfigConnectionString, AppFolder, VDir)
        '    'Sleep()
        '    'SaveASPNetVersion(Site, VDir, TargetASPNETVersion)
        '    'Sleep()
        '    'System.Diagnostics.Process.Start(String.Format("http://localhost/{0}/default.aspx", VDir))

        'End If

    End Sub

    'Private Sub Sleep()
    '    System.Threading.Thread.Sleep(5000)
    'End Sub

    'Private Function GetItem(ByVal Name As String) As String
    '    Try
    '        Dim strm As Stream = GetEmbeddedResourceStream(Name)
    '        Dim reader As StreamReader = New StreamReader(strm)
    '        Return reader.ReadToEnd()
    '    Catch ex As Exception
    '        MsgBox("In GetItem: " & ex.Message)
    '        Throw ex
    '    End Try
    'End Function

    'Protected Function GetEmbeddedResourceStream(ByVal ResourceName As String) As Stream
    '    Dim NameSpacePath As String = System.Reflection.Assembly.GetExecutingAssembly.GetName.Name.ToString

    '    Return System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream(NameSpacePath & "." & ResourceName)
    'End Function

    'Private Sub BuildDatabase(ByVal Connection As String)

    '    Try
    '        ExecuteSql("master", "CREATE DATABASE " & DBName, Connection)
    '    Catch ex As Exception
    '        MsgBox("Build Main DB: " & ex.Message)
    '        Throw ex
    '    End Try

    'End Sub

    'Private Function ExecuteDatabaseScripts(ByVal Scripts As String(), ByVal Connection As String) As Boolean
    '    Try
    '        For Each script As String In Scripts
    '            ExecuteScript(script, Connection)
    '        Next
    '    Catch ex As Exception
    '        MsgBox(String.Format("Installing the database failed." & Microsoft.VisualBasic.Chr(10) & "" & Microsoft.VisualBasic.Chr(10) & " The error message was:" & Microsoft.VisualBasic.Chr(10) & "" & Microsoft.VisualBasic.Chr(10) & "{0}", ex))
    '        Throw ex
    '    End Try
    '    Return True
    'End Function

    'Private Sub GrantAccess(ByVal Connection As String, ByVal User As String)
    '    Try
    '        ExecuteSql(DBName, String.Format("EXEC sp_grantdbaccess '{0}'", User), Connection)
    '    Catch ex As Exception
    '        MsgBox("GrantAccess: " & ex.Message)
    '        Throw ex
    '    End Try
    'End Sub

    'Private Sub GrantObjectAccess(ByVal bGrant As Boolean, ByVal Connection As String, ByVal userName As String)

    '    Dim conn As SqlConnection = New SqlConnection(Connection)

    '    conn.Open()

    '    Dim trans As SqlTransaction = conn.BeginTransaction(IsolationLevel.ReadCommitted)
    '    Dim da As SqlDataAdapter = New SqlDataAdapter("select Name,IsUserTable = OBJECTPROPERTY(id, N'IsUserTable'),IsScalarFunction = OBJECTPROPERTY(id, N'IsScalarFunction'),IsProcedure = OBJECTPROPERTY(id, N'IsProcedure'),IsView = OBJECTPROPERTY(id, N'IsView') from dbo.sysobjects", conn)
    '    da.SelectCommand.Transaction = trans
    '    Dim dt As DataTable = New DataTable("sysobjects")
    '    da.Fill(dt)
    '    Dim cmd As SqlCommand = conn.CreateCommand

    '    Try
    '        cmd.Transaction = trans
    '        cmd.CommandType = CommandType.Text
    '        cmd.CommandText = "select current_user"

    '        If bGrant Then
    '            cmd.CommandType = CommandType.Text
    '            For Each row As DataRow In dt.Rows
    '                If row("IsProcedure").ToString.Equals("1") OrElse row("IsScalarFunction").ToString.Equals("1") Then
    '                    cmd.CommandText = String.Format("grant execute on ""{0}"" to ""{1}""", row("Name"), userName)
    '                    cmd.ExecuteNonQuery()
    '                End If
    '                If row("IsUserTable").ToString.Equals("1") OrElse row("IsView").ToString.Equals("1") Then
    '                    cmd.CommandText = String.Format("grant select,update on ""{0}"" to ""{1}""", row("Name"), userName)
    '                    cmd.ExecuteNonQuery()
    '                End If
    '            Next
    '        Else
    '            cmd.CommandText = "sp_changeobjectowner"
    '            cmd.CommandType = CommandType.StoredProcedure
    '            For Each row As DataRow In dt.Select("IsUserTable=1")
    '                cmd.Parameters.Clear()
    '                cmd.Parameters.AddWithValue("@objname", row("Name"))
    '                cmd.Parameters.AddWithValue("@newowner", "dbo")
    '                Try
    '                    cmd.ExecuteNonQuery()
    '                Catch generatedExceptionVariable0 As SqlException
    '                End Try
    '            Next
    '            For Each row As DataRow In dt.Select("IsView=1")
    '                cmd.Parameters.Clear()
    '                cmd.Parameters.AddWithValue("@objname", row("Name"))
    '                cmd.Parameters.AddWithValue("@newowner", "dbo")
    '                Try
    '                    cmd.ExecuteNonQuery()
    '                Catch generatedExceptionVariable0 As SqlException
    '                End Try
    '            Next
    '        End If
    '    Catch
    '        trans.Rollback()
    '    Finally
    '        trans.Commit()
    '        CType(cmd, IDisposable).Dispose()
    '        CType(dt, IDisposable).Dispose()
    '        CType(da, IDisposable).Dispose()
    '        CType(trans, IDisposable).Dispose()
    '        conn.Close()
    '        CType(conn, IDisposable).Dispose()
    '    End Try

    'End Sub

    'Private Sub ExecuteSql(ByVal DatabaseName As String, ByVal Sql As String, ByVal Connection As String)

    '    Dim Command As New SqlClient.SqlCommand(Sql, New SqlConnection(Connection))

    '    Command.Connection.Open()
    '    Command.Connection.ChangeDatabase(DatabaseName)

    '    Try
    '        Command.ExecuteNonQuery()
    '    Finally
    '        Command.Connection.Close()
    '    End Try

    'End Sub

    'Private Sub ExecuteScript(ByVal sScriptFile As String, ByVal Connection As String)
    '    Dim strm As Stream = GetEmbeddedResourceStream(sScriptFile)
    '    Dim sScript As String = Nothing

    '    Try
    '        Dim file As System.IO.StreamReader = New System.IO.StreamReader(strm)
    '        Try
    '            sScript = file.ReadToEnd + "" & Microsoft.VisualBasic.Chr(13) & "" & Microsoft.VisualBasic.Chr(10) & ""
    '            file.Close()
    '        Finally
    '            CType(file, IDisposable).Dispose()
    '        End Try
    '    Catch generatedExceptionVariable0 As System.IO.FileNotFoundException
    '        Return
    '    Catch x As Exception
    '        Throw New Exception("Failed to read " + sScriptFile, x)
    '    End Try

    '    Dim statements As String() = System.Text.RegularExpressions.Regex.Split(sScript, "\sGO\s", System.Text.RegularExpressions.RegexOptions.IgnoreCase)

    '    Dim conn As SqlConnection = New SqlConnection(Connection)
    '    Try
    '        conn.Open()
    '        Dim trans As SqlTransaction = conn.BeginTransaction("Install")
    '        Try
    '            For Each sql0 As String In statements
    '                Dim sql As String = sql0.Trim
    '                Try
    '                    If sql.ToLower.IndexOf("setuser") >= 0 Then

    '                    End If
    '                    If sql.Length > 0 Then

    '                        Dim cmd As SqlCommand = New SqlCommand
    '                        Try
    '                            cmd.Transaction = trans
    '                            cmd.Connection = conn
    '                            cmd.CommandType = CommandType.Text
    '                            cmd.CommandText = sql.Trim
    '                            cmd.ExecuteNonQuery()
    '                        Finally
    '                            CType(cmd, IDisposable).Dispose()
    '                        End Try
    '                    End If
    '                Catch x As Exception
    '                    trans.Rollback("Install")
    '                    Throw New Exception(String.Format("FILE:" & Microsoft.VisualBasic.Chr(10) & "{0}" & Microsoft.VisualBasic.Chr(10) & "" & Microsoft.VisualBasic.Chr(10) & "ERROR:" & Microsoft.VisualBasic.Chr(10) & "{2}" & Microsoft.VisualBasic.Chr(10) & "" & Microsoft.VisualBasic.Chr(10) & "STATEMENT:" & Microsoft.VisualBasic.Chr(10) & "{1}", sScriptFile, sql, x.Message))
    '                End Try
    '            Next
    '            trans.Commit()
    '            trans.Dispose()
    '        Finally
    '            CType(trans, IDisposable).Dispose()
    '        End Try
    '        conn.Close()
    '        conn.Dispose()
    '    Finally
    '        CType(conn, IDisposable).Dispose()
    '    End Try
    'End Sub

    'Private Sub SaveConnection(ByVal Connection As String, ByVal WebFolder As String, ByVal targetVDir As String)

    '    Try
    '        Dim fileMap As ExeConfigurationFileMap = New ExeConfigurationFileMap
    '        fileMap.ExeConfigFilename = String.Format("{0}\{1}\web.config", WebFolder, targetVDir)
    '        Dim configuration As Configuration = ConfigurationManager.OpenMappedExeConfiguration(fileMap, ConfigurationUserLevel.None)
    '        Dim ConnStringSection As ConnectionStringsSection = CType(configuration.GetSection("connectionStrings"), ConnectionStringsSection)

    '        If Not (ConnStringSection Is Nothing) Then
    '            ConnStringSection.ConnectionStrings("BlazeApps").ConnectionString = Connection
    '            configuration.Save()
    '        End If

    '    Catch ex As Exception
    '        MsgBox("SaveConnection: " & ex.Message)
    '        Throw ex
    '    End Try

    'End Sub

    'Private Sub SaveASPNetVersion(ByVal targetSite As String, ByVal targetVDir As String, ByVal ASPNetVersion As String)
    '    Try

    '        Dim processStartInfo As ProcessStartInfo = New ProcessStartInfo
    '        processStartInfo.FileName = Path.Combine(Environment.GetEnvironmentVariable("SystemRoot"), "Microsoft.NET\Framework\" + ASPNetVersion + "\aspnet_regiis.exe")
    '        processStartInfo.Arguments = String.Format("-s {0}/Root/{1}", Replace(targetSite, "LM/", ""), targetVDir)
    '        processStartInfo.CreateNoWindow = True
    '        processStartInfo.UseShellExecute = False
    '        Process.Start(processStartInfo)

    '    Catch ex As Exception
    '        MsgBox("SaveASPNetVersion: " & ex.Message)
    '        Throw ex
    '    End Try
    'End Sub

    'Protected Function IsDBCurrent(ByVal DBVersion As Decimal, ByVal Connection As String) As Boolean
    '    Dim cmd As New SqlCommand
    '    Dim conn As SqlConnection = New SqlConnection(Connection)
    '    Dim IsCurrent As Boolean = False
    '    Dim DBValue As Decimal = 0

    '    Try
    '        conn.Open()

    '        cmd.Connection = conn
    '        cmd.CommandText = String.Format("select version from dbversion where version = {0}", DBVersion)
    '        cmd.CommandType = CommandType.Text

    '        If Not IsNothing(cmd.ExecuteScalar()) Then
    '            DBValue = Convert.ToDecimal(cmd.ExecuteScalar().ToString)
    '        End If

    '        If DBValue = DBVersion Then
    '            IsCurrent = True
    '        Else
    '            IsCurrent = False
    '        End If

    '        conn.Close()
    '    Catch x As Exception
    '        IsCurrent = False
    '    Finally
    '        CType(conn, IDisposable).Dispose()
    '    End Try

    '    Return IsCurrent
    'End Function

    'Protected Function IsDBBuilt(ByVal Connection As String) As Boolean
    '    Dim cmd As New SqlCommand
    '    Dim conn As SqlConnection = New SqlConnection(Connection)
    '    Dim IsBuilt As Boolean = False
    '    Dim DBValue As String = String.Empty

    '    Try
    '        conn.Open()

    '        cmd.Connection = conn
    '        cmd.CommandText = String.Format("select loweredrolename from aspnet_roles where rolename = 'admin'")
    '        cmd.CommandType = CommandType.Text

    '        If Not IsNothing(cmd.ExecuteScalar()) Then
    '            DBValue = cmd.ExecuteScalar().ToString.Trim
    '        End If

    '        If DBValue.ToLower.Trim = "admin" Then
    '            IsBuilt = True
    '            'MsgBox(String.Format("DB Check is True, Conn String {0}", Connection))
    '        Else
    '            IsBuilt = False
    '            'MsgBox(String.Format("DB Check is False, Conn String {0}", Connection))
    '        End If

    '        conn.Close()
    '    Catch x As Exception
    '        IsBuilt = False
    '        'MsgBox(String.Format("DB Check Errored Out, Conn String {0}", Connection))
    '    Finally
    '        CType(conn, IDisposable).Dispose()
    '    End Try

    '    Return IsBuilt
    'End Function

End Class
